-- ===================================================================
-- ${table.comment!(table.entityName!'')} - 菜单和权限SQL脚本
-- ===================================================================
-- 文件: ${table.entityName}_menu_permission.sql
-- 描述: 为${table.comment!(table.entityName!'')}模块自动生成菜单和权限配置
-- 
-- 遵循Kexio开发规范：
-- - 租户隔离：所有数据都包含tenant_id字段
-- - 审计字段：自动填充create_by、create_time、update_by、update_time
-- - 权限控制：标准的CRUD权限模型
-- - 菜单结构：支持目录-菜单层级结构
--
-- 数据库兼容性：PostgreSQL语法，使用 ON CONFLICT 而非 MySQL 的 ON DUPLICATE KEY UPDATE
--
-- 生成内容：
-- 1. 模块目录菜单（可选，如果未指定父菜单）
-- 2. 功能菜单项
-- 3. 标准CRUD权限：列表、查询、新增、编辑、删除、导入、导出
-- 4. 超级管理员角色权限绑定
--
-- 作者: Kexio代码生成器
-- 日期: ${now?string('yyyy-MM-dd HH:mm:ss')}
-- ===================================================================

<#-- 获取父菜单配置 -->
<#if table.parentMenuId?? && table.parentMenuId?trim != "">
  <#assign parentMenuId = table.parentMenuId>
  <#assign createModuleMenu = false>
<#else>
  <#assign parentMenuId = "menu_" + table.moduleName>
  <#assign createModuleMenu = true>
</#if>

-- ----------------------------
-- 1. 创建模块目录菜单（如果需要）
-- ----------------------------
<#if createModuleMenu>

-- 检查并创建模块目录菜单
INSERT INTO sys_menu (
    id, 
    tenant_id, 
    parent_id, 
    name, 
    title, 
    path, 
    component, 
    icon, 
    menu_type, 
    permission, 
    order_no, 
    is_frame, 
    is_cache, 
    visible, 
    status, 
    query_params, 
    remark, 
    metadata, 
    create_by, 
    create_time, 
    update_by, 
    update_time, 
    deleted
) VALUES (
    '${parentMenuId}',
    'default',
    'menu_system',                           -- 系统管理下级菜单
    '${table.moduleName}',
    '${table.moduleName?cap_first}管理',
    '/${table.moduleName}',
    'Layout',                                -- 目录类型使用Layout组件
    'Folder',                               -- 默认文件夹图标
    'DIR',                                  -- DIR表示目录
    NULL,                                   -- 目录不需要权限
    50,                                     -- 排序号
    false,                                  -- 非外链
    false,                                  -- 不缓存
    true,                                   -- 可见
    1,                                      -- 启用
    NULL,                                   -- 无路由参数
    '${table.comment!(table.entityName!'')}模块目录',
    NULL,                                   -- 无元数据
    'system',                              -- 创建人
    NOW(),                                 -- 创建时间
    'system',                              -- 更新人  
    NOW(),                                 -- 更新时间
    0                                      -- 未删除
) ON CONFLICT (id) DO UPDATE SET 
        title = EXCLUDED.title,
        remark = EXCLUDED.remark,
        update_by = EXCLUDED.update_by,
        update_time = EXCLUDED.update_time;

</#if>

-- ----------------------------
-- 2. 创建功能菜单
-- ----------------------------

INSERT INTO sys_menu (
    id,
    tenant_id,
    parent_id,
    name,
    title,
    path,
    component,
    icon,
    menu_type,
    permission,
    order_no,
    is_frame,
    is_cache,
    visible,
    status,
    query_params,
    remark,
    metadata,
    create_by,
    create_time,
    update_by,
    update_time,
    deleted
) VALUES (
    'menu_${table.moduleName}_${lowerEntity}',
    'default',
    '${parentMenuId}',
    '${table.moduleName}_${lowerEntity}',
    '${table.comment!(table.entityName!'')}',
    '/${table.moduleName}/${lowerEntity}',
    '${table.moduleName}/${lowerEntity}/index',     -- 前端组件路径
    'Document',                                      -- 默认文档图标，可根据业务调整
    'MENU',                                         -- MENU表示菜单
    '${permissionPrefix}:list',                     -- 菜单对应的主权限
    51,                                             -- 排序号
    false,                                          -- 非外链
    true,                                           -- 缓存页面
    true,                                           -- 可见
    1,                                              -- 启用
    NULL,                                           -- 无路由参数
    '${table.comment!(table.entityName!'')}管理页面',
    NULL,                                           -- 无元数据
    'system',                                       -- 创建人
    NOW(),                                          -- 创建时间
    'system',                                       -- 更新人
    NOW(),                                          -- 更新时间
    0                                               -- 未删除
) ON CONFLICT (id) DO UPDATE SET 
        title = EXCLUDED.title,
        path = EXCLUDED.path,
        component = EXCLUDED.component,
        permission = EXCLUDED.permission,
        remark = EXCLUDED.remark,
        update_by = EXCLUDED.update_by,
        update_time = EXCLUDED.update_time;

-- ----------------------------
-- 3. 创建标准CRUD权限
-- ----------------------------

-- 列表查询权限
INSERT INTO sys_permission (
    id,
    tenant_id,
    code,
    name,
    type,
    description,
    path,
    status,
    order_no,
    create_by,
    create_time,
    update_by,
    update_time,
    deleted,
    version,
    remark,
    menu_id
) VALUES (
    'perm_${table.moduleName}_${lowerEntity}_list',
    'default',
    '${permissionPrefix}:list',
    '${table.comment!(table.entityName!'')} - 列表查询',
    'API',
    '查看${table.comment!(table.entityName!'')}列表，支持分页和条件筛选',
    '/api/${table.moduleName}/${lowerEntity}/list',
    1,
    1,
    'system',
    NOW(),
    'system',
    NOW(),
    0,
    0,
    '${table.comment!(table.entityName!'')}列表查询权限',
    'menu_${table.moduleName}_${lowerEntity}'
) ON CONFLICT (id) DO UPDATE SET
    name = EXCLUDED.name,
    description = EXCLUDED.description,
    path = EXCLUDED.path,
    update_by = EXCLUDED.update_by,
    update_time = EXCLUDED.update_time;

-- 详情查询权限  
INSERT INTO sys_permission (
    id,
    tenant_id,
    code,
    name,
    type,
    description,
    path,
    status,
    order_no,
    create_by,
    create_time,
    update_by,
    update_time,
    deleted,
    version,
    remark,
    menu_id
) VALUES (
    'perm_${table.moduleName}_${lowerEntity}_query',
    'default',
    '${permissionPrefix}:query',
    '${table.comment!(table.entityName!'')} - 详情查询',
    'API',
    '查看${table.comment!(table.entityName!'')}详细信息',
    '/api/${table.moduleName}/${lowerEntity}/{id}',
    1,
    2,
    'system',
    NOW(),
    'system',
    NOW(),
    0,
    0,
    '${table.comment!(table.entityName!'')}详情查询权限',
    'menu_${table.moduleName}_${lowerEntity}'
) ON CONFLICT (id) DO UPDATE SET
    name = EXCLUDED.name,
    description = EXCLUDED.description,
    path = EXCLUDED.path,
    update_by = EXCLUDED.update_by,
    update_time = EXCLUDED.update_time;

-- 新增权限
INSERT INTO sys_permission (
    id,
    tenant_id,
    code,
    name,
    type,
    description,
    path,
    status,
    order_no,
    create_by,
    create_time,
    update_by,
    update_time,
    deleted,
    version,
    remark,
    menu_id
) VALUES (
    'perm_${table.moduleName}_${lowerEntity}_add',
    'default',
    '${permissionPrefix}:add',
    '${table.comment!(table.entityName!'')} - 新增',
    'API',
    '创建新的${table.comment!(table.entityName!'')}记录',
    '/api/${table.moduleName}/${lowerEntity}',
    1,
    3,
    'system',
    NOW(),
    'system',
    NOW(),
    0,
    0,
    '${table.comment!(table.entityName!'')}新增权限',
    'menu_${table.moduleName}_${lowerEntity}'
) ON CONFLICT (id) DO UPDATE SET
    name = EXCLUDED.name,
    description = EXCLUDED.description,
    path = EXCLUDED.path,
    update_by = EXCLUDED.update_by,
    update_time = EXCLUDED.update_time;

-- 编辑权限
INSERT INTO sys_permission (
    id,
    tenant_id,
    code,
    name,
    type,
    description,
    path,
    status,
    order_no,
    create_by,
    create_time,
    update_by,
    update_time,
    deleted,
    version,
    remark,
    menu_id
) VALUES (
    'perm_${table.moduleName}_${lowerEntity}_edit',
    'default',
    '${permissionPrefix}:edit',
    '${table.comment!(table.entityName!'')} - 编辑',
    'API',
    '修改${table.comment!(table.entityName!'')}记录信息',
    '/api/${table.moduleName}/${lowerEntity}',
    1,
    4,
    'system',
    NOW(),
    'system',
    NOW(),
    0,
    0,
    '${table.comment!(table.entityName!'')}编辑权限',
    'menu_${table.moduleName}_${lowerEntity}'
) ON CONFLICT (id) DO UPDATE SET
    name = EXCLUDED.name,
    description = EXCLUDED.description,
    path = EXCLUDED.path,
    update_by = EXCLUDED.update_by,
    update_time = EXCLUDED.update_time;

-- 删除权限
INSERT INTO sys_permission (
    id,
    tenant_id,
    code,
    name,
    type,
    description,
    path,
    status,
    order_no,
    create_by,
    create_time,
    update_by,
    update_time,
    deleted,
    version,
    remark,
    menu_id
) VALUES (
    'perm_${table.moduleName}_${lowerEntity}_remove',
    'default',
    '${permissionPrefix}:remove',
    '${table.comment!(table.entityName!'')} - 删除',
    'API',
    '删除${table.comment!(table.entityName!'')}记录（支持批量删除）',
    '/api/${table.moduleName}/${lowerEntity}/{id}',
    1,
    5,
    'system',
    NOW(),
    'system',
    NOW(),
    0,
    0,
    '${table.comment!(table.entityName!'')}删除权限',
    'menu_${table.moduleName}_${lowerEntity}'
) ON CONFLICT (id) DO UPDATE SET
    name = EXCLUDED.name,
    description = EXCLUDED.description,
    path = EXCLUDED.path,
    update_by = EXCLUDED.update_by,
    update_time = EXCLUDED.update_time;

-- 导出权限
INSERT INTO sys_permission (
    id,
    tenant_id,
    code,
    name,
    type,
    description,
    path,
    status,
    order_no,
    create_by,
    create_time,
    update_by,
    update_time,
    deleted,
    version,
    remark,
    menu_id
) VALUES (
    'perm_${table.moduleName}_${lowerEntity}_export',
    'default',
    '${permissionPrefix}:export',
    '${table.comment!(table.entityName!'')} - 导出',
    'API',
    '导出${table.comment!(table.entityName!'')}数据为Excel文件',
    '/api/${table.moduleName}/${lowerEntity}/export',
    1,
    6,
    'system',
    NOW(),
    'system',
    NOW(),
    0,
    0,
    '${table.comment!(table.entityName!'')}导出权限',
    'menu_${table.moduleName}_${lowerEntity}'
) ON CONFLICT (id) DO UPDATE SET
    name = EXCLUDED.name,
    description = EXCLUDED.description,
    path = EXCLUDED.path,
    update_by = EXCLUDED.update_by,
    update_time = EXCLUDED.update_time;

-- 导入权限
INSERT INTO sys_permission (
    id,
    tenant_id,
    code,
    name,
    type,
    description,
    path,
    status,
    order_no,
    create_by,
    create_time,
    update_by,
    update_time,
    deleted,
    version,
    remark,
    menu_id
) VALUES (
    'perm_${table.moduleName}_${lowerEntity}_import',
    'default',
    '${permissionPrefix}:import',
    '${table.comment!(table.entityName!'')} - 导入',
    'API',
    '从Excel文件导入${table.comment!(table.entityName!'')}数据',
    '/api/${table.moduleName}/${lowerEntity}/import',
    1,
    7,
    'system',
    NOW(),
    'system',
    NOW(),
    0,
    0,
    '${table.comment!(table.entityName!'')}导入权限',
    'menu_${table.moduleName}_${lowerEntity}'
) ON CONFLICT (id) DO UPDATE SET
    name = EXCLUDED.name,
    description = EXCLUDED.description,
    path = EXCLUDED.path,
    update_by = EXCLUDED.update_by,
    update_time = EXCLUDED.update_time;

-- ----------------------------
-- 4. 为超级管理员分配权限（可选）
-- ----------------------------
-- 说明：以下SQL用于自动为超级管理员角色分配新创建的权限
-- 如果你的系统使用不同的角色管理方式，请根据实际情况调整

/*
-- 分配菜单权限（如果存在sys_role_menu表）
<#if createModuleMenu>
INSERT IGNORE INTO sys_role_menu (
    role_id, 
    menu_id, 
    tenant_id, 
    create_by, 
    create_time, 
    update_by, 
    update_time, 
    deleted
) VALUES 
('role_super_admin', '${parentMenuId}', 'default', 'system', NOW(), 'system', NOW(), 0);
</#if>

INSERT IGNORE INTO sys_role_menu (
    role_id, 
    menu_id, 
    tenant_id, 
    create_by, 
    create_time, 
    update_by, 
    update_time, 
    deleted
) VALUES 
('role_super_admin', 'menu_${table.moduleName}_${lowerEntity}', 'default', 'system', NOW(), 'system', NOW(), 0);

-- 分配API权限（如果存在sys_role_permission表）
INSERT IGNORE INTO sys_role_permission (
    role_id,
    permission_id,
    tenant_id,
    create_by,
    create_time,
    update_by,
    update_time,
    deleted
) VALUES 
('role_super_admin', 'perm_${table.moduleName}_${lowerEntity}_list', 'default', 'system', NOW(), 'system', NOW(), 0),
('role_super_admin', 'perm_${table.moduleName}_${lowerEntity}_query', 'default', 'system', NOW(), 'system', NOW(), 0),
('role_super_admin', 'perm_${table.moduleName}_${lowerEntity}_add', 'default', 'system', NOW(), 'system', NOW(), 0),
('role_super_admin', 'perm_${table.moduleName}_${lowerEntity}_edit', 'default', 'system', NOW(), 'system', NOW(), 0),
('role_super_admin', 'perm_${table.moduleName}_${lowerEntity}_remove', 'default', 'system', NOW(), 'system', NOW(), 0),
('role_super_admin', 'perm_${table.moduleName}_${lowerEntity}_export', 'default', 'system', NOW(), 'system', NOW(), 0),
('role_super_admin', 'perm_${table.moduleName}_${lowerEntity}_import', 'default', 'system', NOW(), 'system', NOW(), 0);
*/

-- ----------------------------
-- 5. 验证脚本执行结果
-- ----------------------------

-- 查询新增的菜单
SELECT 
    '${table.comment!(table.entityName!'')} - 菜单创建结果' as info,
    COUNT(*) as menu_count 
FROM sys_menu 
WHERE (id = 'menu_${table.moduleName}_${lowerEntity}'<#if createModuleMenu> OR id = '${parentMenuId}'</#if>)
    AND tenant_id = 'default' 
    AND deleted = 0;

-- 查询新增的权限
SELECT 
    '${table.comment!(table.entityName!'')} - 权限创建结果' as info,
    COUNT(*) as permission_count 
FROM sys_permission 
WHERE code LIKE '${permissionPrefix}:%'
    AND tenant_id = 'default' 
    AND deleted = 0;

-- ----------------------------
-- 📋 脚本执行完成说明
-- ----------------------------

/*
🎉 ${table.comment!(table.entityName!'')}模块菜单和权限配置完成！

📋 本次创建内容：
<#if createModuleMenu>
1. 模块目录菜单: ${table.moduleName?cap_first}管理 (/${table.moduleName})
2. 功能菜单: ${table.comment!(table.entityName!'')} (/${table.moduleName}/${lowerEntity})
<#else>
1. 功能菜单: ${table.comment!(table.entityName!'')} (/${table.moduleName}/${lowerEntity})
</#if>

🔐 权限列表：
- ${permissionPrefix}:list - 列表查询
- ${permissionPrefix}:query - 详情查询  
- ${permissionPrefix}:add - 新增
- ${permissionPrefix}:edit - 编辑
- ${permissionPrefix}:remove - 删除
- ${permissionPrefix}:export - 导出
- ${permissionPrefix}:import - 导入

🚀 前端访问路径：
- 页面路径: /${table.moduleName}/${lowerEntity}
- 组件路径: ${table.moduleName}/${lowerEntity}/index

💡 注意事项：
1. 请根据实际需求取消注释第4部分的角色权限分配SQL
2. 如果前端组件路径不同，请手动调整菜单表中的component字段
3. 可以根据业务需求调整菜单图标和排序号
4. 执行后请重启应用以确保菜单正确加载

🔄 下一步操作：
1. 创建对应的前端页面组件
2. 为相应角色分配权限
3. 测试菜单显示和权限控制
*/


